﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using KCL.Code.Entities;
using System.Data;

namespace KCL.Code.Data
{
    public class PhaseDAO : BaseDAO
    {
        public List<Phase> GetPhase(int maximumRows, int startRowIndex, string sortExpression)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("WITH OrderedPhase AS (SELECT *, ROW_NUMBER() OVER (ORDER BY {0}) AS 'RowNumber' FROM [Phase]) SELECT * FROM OrderedPhase WHERE RowNumber BETWEEN {1} AND {2}", sortExpression != string.Empty ? sortExpression : "id", startRowIndex, startRowIndex + maximumRows);
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                connection.Open();
                DataTable dt = new DataTable();

                try
                {
                    sqlAdapter.Fill(dt);
                    List<Phase> results = new List<Phase>();
                    int currentIndex = 0;
                    while (currentIndex < dt.Rows.Count)
                    {
                        Phase phase = new Phase();                        
                        phase.id = Convert.ToInt32(dt.Rows[currentIndex]["id"]);
                        phase.name = dt.Rows[currentIndex]["name"].ToString();
                        phase.price = Convert.ToInt32(dt.Rows[currentIndex]["price"]);                       
                        results.Add(phase);
                        currentIndex++;                        
                    }
                    return results;
                }
                catch 
                {
                    return null;
                }
            }
        }

        public int GetTotalPhase()
        {
            using (SqlConnection connection = GetConnection())
            {                
                string sqlStatement = "SELECT COUNT(*) FROM [Phase]";
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                connection.Open();
                int totalUser = Convert.ToInt32(command.ExecuteScalar());                
                return totalUser;
            }
        }

        public bool CreatePhase(Phase phase)
        { 
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("INSERT INTO [Phase]([name], [price]) VALUES(N'{0}', N'{1}')", phase.name, phase.price);
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch
                {
                    return false;
                }                   
            }
        }

        public bool UpdatePhase(Phase phase)
        {
            SqlConnection connection = this.GetConnection();
            string sqlStatement = string.Format("UPDATE [Phase] SET name=N'{0}', price=N'{1}' WHERE id={2}", phase.name, phase.price, phase.id);
            SqlCommand command = new SqlCommand(sqlStatement, connection);
            try
            {
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch
            {
                return false;
            }
            return true;
        }

		public List<Phase> GetAllPhases()
		{
			using (SqlConnection connection = GetConnection())
			{
				string sqlStatement = "SELECT * FROM Phase ORDER BY [name]";
				SqlCommand command = new SqlCommand(sqlStatement, connection);
				SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
				connection.Open();
				DataTable dt = new DataTable();

				try
				{
					sqlAdapter.Fill(dt);
					List<Phase> results = new List<Phase>();
					int currentIndex = 0;
					while (currentIndex < dt.Rows.Count)
					{
						Phase phase = new Phase();
						phase.id = Convert.ToInt32(dt.Rows[currentIndex]["id"]);
						phase.name = dt.Rows[currentIndex]["name"].ToString();
						phase.price = Convert.ToInt32(dt.Rows[currentIndex]["price"]);
						results.Add(phase);
						currentIndex++;
					}
					return results;
				}
				catch
				{
					return null;
				}
			}
		}
    }
}